﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Server.Models;
using Server.Util;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Data;

namespace Server.UserService
{
    public class UserDAL
    {
        CacheUtil cacheCtrl = new CacheUtil();
        DBUtil dbCtrl = new DBUtil();
        public void UserAdd(UserInfo user)
        {
            try
            {
                string sqlstr = " Insert into Manage_User(LoginName,Password,Sault,State,MCompanyId,MCompanyName,DeviceId,DeviceRemark,DeviceSession,SessionExpired,ExpireTime,CompanyName,ContactName,TelNum,MobNum,Address,ProvinceId,ProvinceName,CityId,CityName,DistrictId,DistrictName,TaxNum,BankName,BankNum,SBProvinceId,SBProvinceName,SBCityId,SBCityName,SBId,SBName,TradeBigId,TradeBigName,TradeMidId,TradeMidName,TradeSmallId,TradeSmallName) values(@LoginName,@Password,@Sault,@State,@MCompanyId,@MCompanyName,@DeviceId,@DeviceRemark,@DeviceSession,@SessionExpired,@ExpireTime,@CompanyName,@ContactName,@TelNum,@MobNum,@Address,@ProvinceId,@ProvinceName,@CityId,@CityName,@DistrictId,@DistrictName,@TaxNum,@BankName,@BankNum,@SBProvinceId,@SBProvinceName,@SBCityId,@SBCityName,@SBId,@SBName,@TradeBigId,@TradeBigName,@TradeMiId,@TradeMidName,@TradeSmallId,@TradeSmallName) ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@LoginName",Value = user.LoginName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Password",Value =user.Password},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Sault",Value = user.Sault },
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@State",Value = user.State},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@MCompanyId",Value = user.MCompanyId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@MCompanyName",Value = user.MCompanyName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@DeviceId",Value = user.DeviceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceRemark",Value = user.DeviceRemark},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceSession",Value = user.DeviceSession},
                    new SqlParameter{SqlDbType = SqlDbType.DateTime,ParameterName = "@SessionExpired",Value = user.SessionExpired},
                    new SqlParameter{SqlDbType = SqlDbType.DateTime,ParameterName = "@ExpireTime",Value = user.ExpireTime},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@CompanyName",Value = user.CompanyName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@ContactName",Value = user.ContactName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TelNum",Value = user.TelNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@MobNum",Value = user.MobNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Address",Value = user.Address},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@ProvinceId",Value = user.ProvinceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@ProvinceName",Value = user.ProvinceName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@CityId",Value = user.CityId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@CityName",Value = user.CityName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@DistrictId",Value = user.DistrictId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DistrictName",Value = user.DistrictName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxNum",Value = user.TaxNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@BankName",Value = user.BankName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@BankNum",Value = user.BankNum},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBProvinceId",Value = user.SBProvinceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBProvinceName",Value = user.SBProvinceName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBCityId",Value = user.SBCityId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBCityName",Value = user.SBCityName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBId",Value = user.SBId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBName",Value = user.SBName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeBigId",Value = user.TradeBigId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeBigName",Value = user.TradeBigName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeMidId",Value = user.TradeMidId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeMidName",Value = user.TradeMidName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeSmallId",Value = user.TradeSmallId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeSmallName",Value = user.TradeSmallName},
                };
                dbCtrl.Exec(sqlstr, op);
                this.CacheUser();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void UserEdit(UserInfo user)
        {
            try
            {
                string sqlstr = " update Manage_User set LoginName=@LoginName,Password=@Password,Sault=@Sault,State=@State,MCompanyId=@MCompanyId,MCompanyName=@MCompanyName,DeviceId=@DeviceId,DeviceRemark=@DeviceRemark,DeviceSession=@DeviceSession,SessionExpired=@SessionExpired,ExpireTime=@ExpireTime,CompanyName=@CompanyName,ContactName=@ContactName,TelNum=@TelNum,MobNum=@MobNum,Address=@Address,ProvinceId=@ProvinceId,ProvinceName=@ProvinceName,CityId=@CityId,CityName=@CityName,DistrictId=@DistrictId,DistrictName=@DistrictName,TaxNum=@TaxNum,BankName=@BankName,BankNum=@BankNum,SBProvinceId=@SBProvinceId,SBProvinceName=@SBProvinceName,SBCityId=@SBCityId,SBCityName=@SBCityName,SBId=@SBId,SBName=@SBName,TradeBigId=@TradeBigId,TradeBigName=@TradeBigName,TradeMidId=@TradeMidId,TradeMidName=@TradeMidName,TradeSmallId=@TradeSmallId,TradeSmallName=@TradeSmallName ";
                SqlParameter[] op = new SqlParameter[]
               {
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@LoginName",Value = user.LoginName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Password",Value =user.Password},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Sault",Value = user.Sault },
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@State",Value = user.State},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@MCompanyId",Value = user.MCompanyId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@MCompanyName",Value = user.MCompanyName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@DeviceId",Value = user.DeviceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceRemark",Value = user.DeviceRemark},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceSession",Value = user.DeviceSession},
                    new SqlParameter{SqlDbType = SqlDbType.DateTime,ParameterName = "@SessionExpired",Value = user.SessionExpired},
                    new SqlParameter{SqlDbType = SqlDbType.DateTime,ParameterName = "@ExpireTime",Value = user.ExpireTime},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@CompanyName",Value = user.CompanyName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@ContactName",Value = user.ContactName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TelNum",Value = user.TelNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@MobNum",Value = user.MobNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@Address",Value = user.Address},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@ProvinceId",Value = user.ProvinceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@ProvinceName",Value = user.ProvinceName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@CityId",Value = user.CityId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@CityName",Value = user.CityName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@DistrictId",Value = user.DistrictId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@DistrictName",Value = user.DistrictName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxNum",Value = user.TaxNum},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@BankName",Value = user.BankName},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@BankNum",Value = user.BankNum},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBProvinceId",Value = user.SBProvinceId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBProvinceName",Value = user.SBProvinceName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBCityId",Value = user.SBCityId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBCityName",Value = user.SBCityName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@SBId",Value = user.SBId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@SBName",Value = user.SBName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeBigId",Value = user.TradeBigId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeBigName",Value = user.TradeBigName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeMidId",Value = user.TradeMidId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeMidName",Value = user.TradeMidName},
                    new SqlParameter{SqlDbType = SqlDbType.Int,ParameterName = "@TradeSmallId",Value = user.TradeSmallId},
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@TradeSmallName",Value = user.TradeSmallName},
               };
                dbCtrl.Exec(sqlstr, op);
                this.CacheUser();

            }
            catch (Exception)
            {
                throw;
            }
        }
        public void UserState(UserInfo user)
        {
            try
            {
                string sqlstr = string.Format(" update Manage_User set State = {0} where id = {1} ", user.State, user.Id);
                dbCtrl.Exec(sqlstr);
                this.CacheUser();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public UserInfo GetUserByID(int id)
        {
            try
            {
                string tmpStr = (string)cacheCtrl.CacheGet("Manage_User:ID" + id);
                if (string.IsNullOrEmpty(tmpStr))
                {
                    this.CacheUserByID();
                    tmpStr = (string)cacheCtrl.CacheGet("Manage_User:ID" + id);
                }
                tmpStr = JsonConvert.DeserializeObject<string>(tmpStr);
                if (string.IsNullOrEmpty(tmpStr))
                    return null;
                UserInfo ui = JsonConvert.DeserializeObject<UserInfo>(tmpStr);
                return ui;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public UserInfo[] GetUserList()
        {
            try
            {
                string tmpStr = (string)cacheCtrl.CacheGet("Manage_User:List");
                if (string.IsNullOrEmpty(tmpStr))
                {
                    this.CacheUser();
                    tmpStr = (string)cacheCtrl.CacheGet("Manage_User:List");
                }
                tmpStr = JsonConvert.DeserializeObject<string>(tmpStr);
                if (string.IsNullOrEmpty(tmpStr))
                    return null;
                UserInfo[] ui = JsonConvert.DeserializeObject<UserInfo[]>(tmpStr);
                return ui;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void CacheUser()
        {
            try
            {
                string sqlstr = " select * from Manage_User  ";
                var dt = dbCtrl.Query(sqlstr);
                UserInfo[] ui = new UserInfo[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var dr = dt.Rows[i];
                    ui[i] = new UserInfo()
                    {
                        Id = Convert.ToInt32(dr["id"]),
                        LoginName = dr["LoginName"].ToString(),
                        Password = dr["Password"].ToString(),
                        Sault = dr["Sault"].ToString(),
                        State = Convert.ToInt32(dr["State"]),
                        MCompanyId = Convert.ToInt32(dr["MCompanyId"]),
                        MCompanyName = dr["MCompanyName"].ToString(),
                        DeviceId = Convert.ToInt32(dr["DeviceId"]),
                        DeviceRemark = dr["DeviceRemark"].ToString(),
                        DeviceSession = dr["DeviceSession"].ToString(),
                        SessionExpired = Convert.ToDateTime(dr["SessionExpired"]),
                        ExpireTime = Convert.ToDateTime(dr["ExpireTime"]),
                        CompanyName = dr["CompanyName"].ToString(),
                        ContactName = dr["ContactName"].ToString(),
                        TelNum = dr["TelNum"].ToString(),
                        MobNum = dr["MobNum"].ToString(),
                        Address = dr["Address"].ToString(),
                        ProvinceId = Convert.ToInt32("ProvinceId"),
                        ProvinceName = dr["ProvinceName"].ToString(),
                        CityId = Convert.ToInt32(dr["CityId"]),
                        CityName = dr["CityName"].ToString(),
                        DistrictId = Convert.ToInt32(dr["DistrictId"]),
                        DistrictName = dr["DistrictName"].ToString(),
                        TaxNum = dr["TaxNum"].ToString(),
                        BankName = dr["BankName"].ToString(),
                        BankNum = dr["BankNum"].ToString(),
                        SBProvinceId = Convert.ToInt32(dr["SBProvinceId"]),
                        SBProvinceName = dr["SBProvinceName"].ToString(),
                        SBCityId = Convert.ToInt32(dr["SBCityId"]),
                        SBCityName = dr["SBCityName"].ToString(),
                        SBId = Convert.ToInt32(dr["SBId"]),
                        SBName = dr["SBName"].ToString(),
                        TradeBigId = dr["TradeBigId"].ToString(),
                        TradeBigName = dr["TradeBigName"].ToString(),
                        TradeMidId = dr["TradeMidId"].ToString(),
                        TradeMidName = dr["TradeMidName"].ToString(),
                        TradeSmallId = dr["TradeSmallId"].ToString(),
                        TradeSmallName = dr["TradeSmallName"].ToString()
                    };
                }
                cacheCtrl.CacheSet("Manage_User:List", ui);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void CacheUserByID()
        {
            try
            {
                string sqlstr = " select * from Manage_User  ";
                var dt = dbCtrl.Query(sqlstr);
                UserInfo[] ui = new UserInfo[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var dr = dt.Rows[i];
                    ui[i] = new UserInfo()
                    {
                        Id = Convert.ToInt32(dr["id"]),
                        LoginName = dr["LoginName"].ToString(),
                        Password = dr["Password"].ToString(),
                        Sault = dr["Sault"].ToString(),
                        State = Convert.ToInt32(dr["State"]),
                        MCompanyId = Convert.ToInt32(dr["MCompanyId"]),
                        MCompanyName = dr["MCompanyName"].ToString(),
                        DeviceId = Convert.ToInt32(dr["DeviceId"]),
                        DeviceRemark = dr["DeviceRemark"].ToString(),
                        DeviceSession = dr["DeviceSession"].ToString(),
                        SessionExpired = Convert.ToDateTime(dr["SessionExpired"]),
                        ExpireTime = Convert.ToDateTime(dr["ExpireTime"]),
                        CompanyName = dr["CompanyName"].ToString(),
                        ContactName = dr["ContactName"].ToString(),
                        TelNum = dr["TelNum"].ToString(),
                        MobNum = dr["MobNum"].ToString(),
                        Address = dr["Address"].ToString(),
                        ProvinceId = Convert.ToInt32("ProvinceId"),
                        ProvinceName = dr["ProvinceName"].ToString(),
                        CityId = Convert.ToInt32(dr["CityId"]),
                        CityName = dr["CityName"].ToString(),
                        DistrictId = Convert.ToInt32(dr["DistrictId"]),
                        DistrictName = dr["DistrictName"].ToString(),
                        TaxNum = dr["TaxNum"].ToString(),
                        BankName = dr["BankName"].ToString(),
                        BankNum = dr["BankNum"].ToString(),
                        SBProvinceId = Convert.ToInt32(dr["SBProvinceId"]),
                        SBProvinceName = dr["SBProvinceName"].ToString(),
                        SBCityId = Convert.ToInt32(dr["SBCityId"]),
                        SBCityName = dr["SBCityName"].ToString(),
                        SBId = Convert.ToInt32(dr["SBId"]),
                        SBName = dr["SBName"].ToString(),
                        TradeBigId = dr["TradeBigId"].ToString(),
                        TradeBigName = dr["TradeBigName"].ToString(),
                        TradeMidId = dr["TradeMidId"].ToString(),
                        TradeMidName = dr["TradeMidName"].ToString(),
                        TradeSmallId = dr["TradeSmallId"].ToString(),
                        TradeSmallName = dr["TradeSmallName"].ToString()
                    };
                    cacheCtrl.CacheSet("Manage_User:ID:" + ui[i].Id, ui[i]);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #region 设备管理
        public void DeviceAdd(DeviceInfoModel dev)
        {
            try
            {
                string sqlstr = " insert into Manage_DeviceInfo(DeviceSession,SessionExpired,DeviceIP,Number,Remark) values(@DeviceSession,@SessionExpired,@DeviceIP,@Number,@Remark) ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter{ SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceSession",Value = "" },
                    new SqlParameter{ SqlDbType = SqlDbType.DateTime,ParameterName = "@SessionExpired",Value = default(DateTime) },
                    new SqlParameter{ SqlDbType = SqlDbType.VarChar,ParameterName = "@DeviceIP",Value = dev.DeviceIP },
                    new SqlParameter{ SqlDbType = SqlDbType.VarChar,ParameterName = "@Number",Value = dev.Number },
                    new SqlParameter{ SqlDbType = SqlDbType.VarChar,ParameterName = "@Remark",Value = dev.Remark },
                };
                dbCtrl.Exec(sqlstr, op);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public void DeviceDel(int id)
        {
            try
            {
                string sqlstr = " delete from Manage_DeviceInfo where id =  " + id;
                dbCtrl.Exec(sqlstr);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public DataTable GetDeviceList()
        {
            try
            {
                string sqlstr = " select * from Manage_DeviceInfo ";
                var dt = dbCtrl.Query(sqlstr);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion
    }
}
